
import pymysql
class orm:
    @classmethod
    def pool(cls, ):
        BL_MYSQL_CONF = {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'root', 'db': 'image',
                         'charset': 'utf8mb4', 'cursorclass': pymysql.cursors.DictCursor}
        connection = pymysql.connect(**BL_MYSQL_CONF)
        return connection
    @classmethod
    def insert_image(cls, article_image):
        conn = orm.pool()
        cursor = conn.cursor()
        sql = 'insert into image (uid,user_name,insert_date,url,city) VALUES (%s,%s,%s,%s,%s)'
        cursor.execute(sql,
                       (article_image['uid'], article_image['user_name'], article_image['insert_date'],
                        article_image['url'],article_image['addr']))
        id = cursor.lastrowid
        conn.commit()
        cursor.close()
        conn.cursor()
        return id
    @classmethod
    def get_image(cls, p):
        conn = orm.pool()
        cursor = conn.cursor()
        offset=(p-1)*20
        sql = 'select * from image order by id desc limit 20  offset %s '
        print((int(p)-1)*20)
        cursor.execute(sql,offset)
        result = cursor.fetchall()
        return result
    @classmethod
    def get_image_all(cls,):
        conn = orm.pool()
        cursor = conn.cursor()
        sql = 'select count(1) as num  from image'
        cursor.execute(sql)
        result = cursor.fetchone()
        return result
    @classmethod
    def get_image_all_times(cls,date=''):
        conn = orm.pool()
        cursor = conn.cursor()
        if date:
            sql = 'select  uid,user_name,count(id) as num from image where insert_date=%s  group by uid , user_name'
            cursor.execute(sql,date)
        else:
            sql = 'select uid,user_name,count(id) as num from image group by uid , user_name '
            cursor.execute(sql)

        result = cursor.fetchall()
        return result
if __name__ == '__main__':
         print(orm.get_image_all_times(''))